﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace LabTurtorial
{
    public partial class Lab06DataBaseDemo : Form
    {
        private SqlConnection connect;        
        private SqlCommand comand;
        private SqlDataReader reader;

       

        //"Data Source=LONGDT\\SQLEXPRESS;Initial Catalog=Lab06_AutoMobile;Persist Security Info=True;User ID=sa;Password=13021988";

        
        private string strconection = "Data Source=LONGDT\\SQLEXPRESS;Initial Catalog=Lab06_AutoMobile;Persist Security Info=True;User ID=sa;Password=13021988";

        public Lab06DataBaseDemo()
        {
            InitializeComponent();
            


        }
        private void Lab06DataBaseDemo_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'lab06_AutoMobileDataSet.Account' table. You can move, or remove it, as needed.
            this.accountTableAdapter.Fill(this.lab06_AutoMobileDataSet.Account);
            // TODO: This line of code loads data into the 'lab06_AutoMobileDataSet.ProductDetail' table. You can move, or remove it, as needed.
            this.productDetailTableAdapter.Fill(this.lab06_AutoMobileDataSet.ProductDetail);

        }
        List<Account> getAllAccountFromDB()
        {
            List<Account> list = new List<Account>();
            //o day su dung using(){} de co cau truc gon gang
            using (connect = new SqlConnection(strconection))
            {
                connect.Open();
                string query = "SELECT *FROM Account"; // ~ command.CommandText = "SELECT* FROM Account";
                using (comand = new SqlCommand(query, connect))
                {
                    reader = comand.ExecuteReader();
                    while (reader.Read())
                    {
                        string username = reader.GetString(0);
                        string password = reader.GetString(1);
                        Account a = new Account()
                        {
                            Username = username,
                            Password = password
                        };
                        list.Add(a);
                    }
                }
                connect.Close();
                reader.Close();
                return list;
            }

        }
        // User Stored Procedure
        
        List<Account> getAllAccountFromDBUseStoredProcedure() {
            List<Account> list = new List<Account>();
            using (connect = new SqlConnection(strconection)) {
                connect.Open();
                using (comand = new SqlCommand("sp_GetAllAccount",connect)) // stored procedure name & connection to DB
                {
                    comand.CommandType = CommandType.StoredProcedure;
                    reader = comand.ExecuteReader();
                    while (reader.Read())
                    {
                        string username = reader.GetString(0);
                        string password = reader.GetString(1);
                        Account a = new Account()
                        {
                            Username = username,
                            Password = password
                        };
                        list.Add(a);
                    }
                }
                reader.Close();
                connect.Close();
                return list;
            }
            
        }
        void fillListView() {
            listView1.Items.Clear();
            //List<Account> list = getAllAccountFromDB();
            List<Account> list = getAllAccountFromDBUseStoredProcedure();
            int row = 0;
            foreach (Account a in list)
            {
                listView1.Items.Add(a.Username);
                listView1.Items[row].SubItems.Add(a.Password);
                row++;
            }
        }
        private void btnFill_Click(object sender, EventArgs e)
        {
            fillListView();
        }

        //Add new Account use Stored Procedure
        bool AddNewAccount(Account a) {
            int i = 0;
            try
            {
                using (connect = new SqlConnection(strconection))
                {
                    connect.Open();
                    using (comand = new SqlCommand("sp_InsertNewAccount", connect))
                    {
                        comand.CommandType = CommandType.StoredProcedure;
                        comand.Parameters.Add(new SqlParameter("@user", a.Username));
                        comand.Parameters.Add(new SqlParameter("@pass", a.Password));
                        i = comand.ExecuteNonQuery();
                        connect.Close();
                        
                    }

                }
            }
            catch (SqlException ex) {
                MessageBox.Show(ex.Message);
            }
            return i > 0;
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            Account a = new Account() { 
                Username =  txtUsername.Text,
                Password = txtPassword.Text
            };
            if (AddNewAccount(a))
            {
                MessageBox.Show("Success");
                fillListView();
            }
            else {
                MessageBox.Show("Failure");
            }
        }


    }


    class Account
    {
        private string username;

        public string Username
        {
            get { return username; }
            set { username = value; }
        }
        private string password;

        public string Password
        {
            get { return password; }
            set { password = value; }
        }
    }
}
